﻿
USE QLLVANTAI


if exists(Select * from sysobjects where name = 'sp_LichLamViec')
begin
drop proc sp_LichLamViec
end
Go
create proc sp_LichLamViec
	@manv smallint
as
	begin 
		SELECT lvc.Ma_VanChuyen as MaVanChuyen,dk.Ma_Xe as MaXe, td.Diem_XuatPhat as DiemXuatPhat, td.Diem_Den as DiemDen,
		lvc.Gio_Di as GioDi , lvc.Gio_Den as GioDen
		FROM LichVanChuyen lvc,TuyenDuong td,DangKy dk
		WHERE dk.Ma_NV = @manv
		AND lvc.Ma_VanChuyen = dk.Ma_VanChuyen
		AND lvc.Ma_TuyenDuong = td.Ma_TuyenDuong
		AND datediff(d,GETDATE(),lvc.Gio_Di) > 0
	end
go




if exists(Select * from sysobjects where name = 'sp_DangNhap')
begin
drop proc sp_DangNhap
end
go
create proc sp_DangNhap
	@user nvarchar(32),
	@pass nvarchar(32),
	@msg nvarchar(150) OUTPUT
as
	if(not exists (SELECT * FROM NhanVien WHERE UserName = @user))
	begin 
		SET @msg = N'Tài khoản "' + @user + N'" không tồn tại'
		return
	end
	if(not exists (SELECT * FROM NhanVien WHERE UserName = @user AND MatKhau = @pass))
	begin
		SET @msg =  N'Nhập sai mật khẩu'
		return
	end
	SELECT * FROM NhanVien WHERE UserName = @user AND MatKhau = @pass
		SET @msg =  N'Đăng nhập thành công'
			
go

if exists(Select * from sysobjects where name = 'sp_HuyDangKy')
begin
drop proc sp_HuyDangKy
end
go
create proc sp_HuyDangKy
	@manv smallint,
	@mavc int,
	@maxe int,
	@vaitro nvarchar(32),
	@msg nvarchar(150) OUTPUT
as
	declare @Gio_Di smalldatetime
	declare @KT_DK int
	
	SELECT @Gio_Di = Gio_Di
	FROM LichVanChuyen
	WHERE Ma_VanChuyen = @mavc
	
	set @KT_DK = datediff(d,GETDATE(),@Gio_Di)
	
	if(@KT_DK > 10)
	BEGIN
		DELETE FROM DangKy
		WHERE Ma_NV = @manv 
		AND Ma_VanChuyen = @mavc
		AND Ma_Xe = @maxe
		
		if(@vaitro = 1)
		BEGIN
			UPDATE ChiTietLich
			SET So_TaiXeTrong = So_TaiXeTrong + 1
			WHERE Ma_VanChuyen = @mavc
			AND Ma_Xe = @maxe
		END
		
		else if(@vaitro = 2)
		BEGIN
			UPDATE ChiTietLich
			SET So_PhuLaiTrong = So_PhuLaiTrong + 1
			WHERE Ma_VanChuyen = @mavc
			AND Ma_Xe = @maxe			
		END
		
		UPDATE LichVanChuyen
		SET TinhTrang = 0
		WHERE Ma_VanChuyen = @mavc
		
		SET @msg =  N'Hủy thành công'	
		print N'Hủy thành công'
	END
	else
	BEGIN
		SET @msg =  N'Đã quá thời gian để hủy đăng ký !' + CHAR(13) + CHAR(10) + N'Liên hệ với quản lý nếu bạn muốn hủy.'
		print N'Đã quá thời gian để hủy đăng ký !' + CHAR(13) + CHAR(10) + N'Liên hệ với quản lý nếu bạn muốn hủy.'	
	END
go			

  -----------------------
if exists(Select * from sysobjects where name = 'sp_LichLamViecAll')
begin
drop proc sp_LichLamViecAll
end
Go
create proc sp_LichLamViecAll
as
	begin 
	-- CONVERT(VARCHAR(10), Ngay_Di, 103) as NgayDi
	--  CONVERT(VARCHAR(20), SYSDATETIME(), 22)
		SELECT Ma_VanChuyen as MaVanChuyen, Gio_Di as GioDi,
		 Gio_Den as GioDen,Ma_TuyenDuong as MaTuyenDuong, TinhTrang
		FROM LichVanChuyen
	end
go
 

-------------------------
if exists(Select * from sysobjects where name = 'sp_layDanhSachXeRanh')
begin
drop proc sp_layDanhSachXeRanh
end
Go
create proc sp_layDanhSachXeRanh
@giodi datetime
as
	begin 
		Select *  from Xe Where Ma_Xe in
							(select Ma_Xe from LichVanChuyen lvc, ChiTietLich ctl 
							Where lvc.Ma_VanChuyen = ctl.Ma_VanChuyen and Gio_Di != @giodi ) 
							or Ma_Xe not in(select Ma_Xe from LichVanChuyen lvc, ChiTietLich ctl 
							Where lvc.Ma_VanChuyen = ctl.Ma_VanChuyen )
	end
go


if exists(Select * from sysobjects where name = 'sp_ThemLichMoi')
begin
drop proc sp_ThemLichMoi
end
go
create proc sp_ThemLichMoi
	
	@giodi datetime,
	@gioden datetime,
	@tinhtrang int,
	@idtuyenduong int
	
as
	
	BEGIN
		Insert into LichVanChuyen(Gio_Di,Gio_Den,Ma_TuyenDuong,TinhTrang) values(@giodi,@gioden,@idtuyenduong,@tinhtrang)
		
	END

go	

if exists(Select * from sysobjects where name = 'sp_CapNhatLich')
begin
drop proc sp_CapNhatLich
end
go
create proc sp_CapNhatLich
	@malich int,
	@giodi datetime,
	@gioden datetime,
	@tinhtrang int,
	@idtuyenduong int
	
as
	BEGIN
		update LichVanChuyen set Gio_Di = @giodi, Gio_Den = @gioden,  Ma_TuyenDuong = @idtuyenduong, TinhTrang = @tinhtrang
		where Ma_VanChuyen = @malich
	END

go	

if exists(Select * from sysobjects where name = 'sp_XoaLich')
begin
drop proc sp_XoaLich
end
go
create proc sp_XoaLich
	@malich int
as
	BEGIN
		delete from DangKy where Ma_VanChuyen = @malich
		delete from ChiTietLich where Ma_VanChuyen = @malich
		delete from LichVanChuyen where Ma_VanChuyen = @malich
	END
go

if exists(Select * from sysobjects where name = 'sp_ThemXeVaoLich')
begin
drop proc sp_ThemXeVaoLich
end
go
create proc sp_ThemXeVaoLich
	@malich int,@maxe int,@taixe int,@phulai int,@taixetrong int,@phulaitrong int,
	@loai int
as
	if(@loai = 1)
	--update ChiTietLich set So_TaiXe = @taixe, So_PhuLai = @phulai, So_TaiXeTrong = @taixetrong, So_PhuLaiTrong = @phulaitrong where Ma_VanChuyen = @malich
	BEGIN
		
		insert into ChiTietLich values(@malich,@maxe,@taixe,@phulai,@taixetrong,@phulaitrong)
	END
go

if exists(Select * from sysobjects where name = 'sp_XoaChiTietLich')
begin
drop proc sp_XoaChiTietLich
end
go
create proc sp_XoaChiTietLich
	@malich int
as
	--update ChiTietLich set So_TaiXe = @taixe, So_PhuLai = @phulai, So_TaiXeTrong = @taixetrong, So_PhuLaiTrong = @phulaitrong where Ma_VanChuyen = @malich
	BEGIN
		delete from ChiTietLich where Ma_VanChuyen = @malich
	END
go

if exists(Select * from sysobjects where name = 'sp_CapNhatTaiXePhuLaiTrong')
begin
drop proc sp_CapNhatTaiXePhuLaiTrong
end
go
create proc sp_CapNhatTaiXePhuLaiTrong
	@malich int
as
	--update ChiTietLich set So_TaiXe = @taixe, So_PhuLai = @phulai, So_TaiXeTrong = @taixetrong, So_PhuLaiTrong = @phulaitrong where Ma_VanChuyen = @malich
	BEGIN
		declare @taixe int
		declare @phulai int
		declare @maxe char(10)
		declare @cur cursor
		set @cur  = cursor for select distinct Ma_Xe from ChiTietLich where Ma_VanChuyen = 2
		open @cur
		fetch next from @cur into @maxe
		while (@@FETCH_STATUS = 0)
			begin
				set @taixe =(select  COUNT(VaiTro) from DangKy where Ma_VanChuyen = @malich and Ma_Xe = @maxe and VaiTro = 1)
				set @phulai =(select  COUNT(VaiTro) from DangKy where Ma_VanChuyen = @malich and Ma_Xe = @maxe and VaiTro = 2)
				
				update ChiTietLich set So_TaiXeTrong = So_TaiXe - @taixe, So_PhuLaiTrong=So_PhuLai - @phulai
				fetch next from @cur into @maxe 
			end
		close @cur
		deallocate @cur
	END
go

--exec sp_CapNhatTaiXePhuLaiTrong
--select * from DangKy
--select * from ChiTietLich
--print 'kaka' + '  kakaka'


